﻿using System;
using System.Collections.Generic;
using System.Text;
using Orid.Data.SqlServer;
using System.Data;

namespace Orid.Management.Statistics.Tools.Decomposition.Data
{
    /// <summary>
    /// 需要分解的受番
    /// </summary>
     class DecompositionOrders:DataBase
     {
         #region 虚函数

        public virtual DataSet GetDecompositionOrders(DateTimeOffset? starTime, DateTimeOffset? finishTime)
        {
           return null;
        }
        public virtual DataSet GetAllOrders()
        {
            return null;
        }

        public virtual DataSet GetDecompositionOrders(List<Guid> orders)
        {
            return null;
        }

       public virtual void SetDecompositionOrders()
       { }

       #endregion

       #region 方法实现

       protected   DataSet GetAllOrders(string connectString,string tableName)
       {
           try
           {
               DataSet DSDecompositionOrders;

               StringBuilder sqlText = new StringBuilder();
               sqlText.Append(" SELECT OrderID,MatterName,OrderDateTime,FinishDateTime");
               sqlText.Append(" FROM   " + tableName + "  [View_Orders] with(NOLOCK)");//readpast
               sqlText.Append(" ORDER BY OrderDateTime DESC");


               using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectString))
               {
                   conn.Open();


                   DSDecompositionOrders = SqlHelper.ExecuteDataset(conn, CommandType.Text, sqlText.ToString());
                   if (conn.State == System.Data.ConnectionState.Open)
                       conn.Close();

                   return DSDecompositionOrders;
               }
           }
           catch (System.Data.SqlClient.SqlException ex)
           {
               throw (new Exception(ex.Message));
           }


       }

        /// <summary>
        /// 需要表名：DecompositionOrders
        /// 需要的字段: 受番ID，案件名，受付时间(OrderDateTime),完成时间(FinishDateTime)
        /// FinishTime 完成时间符合条件的
        /// 之前没有处理的受番
        /// </summary>
        /// <param name="starTime"></param>
        /// <param name="finishTime"></param>
        /// <param name="connectString"></param>
        /// <param name="tableName"></param>
        protected  DataSet GetDecompositionOrders(
           DateTimeOffset? starTime, DateTimeOffset? finishTime
           ,string connectString,string tableName
           )
       {
           try
           {
               DataSet DSDecompositionOrders;
               if ((starTime == null) || (finishTime == null))
               {
                   return null;
               }

            

               StringBuilder sqlText = new StringBuilder();
               sqlText.Append(" SELECT OrderID,MatterName,OrderDateTime,FinishDateTime");
               sqlText.Append(" FROM   " + tableName + " [View_Orders] with(NOLOCK)");//readpast
               sqlText.Append(" WHERE FinishDateTime >= isnull( @StarTime,'2000-01-01') and FinishDateTime < isnull( @FinishTime ,'2999-01-01')");

               using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectString))
               {
                   conn.Open();
                   System.Data.SqlClient.SqlParameter[] SqlParameter;
                   SqlParameter = new System.Data.SqlClient.SqlParameter[]
                                 {     
                                       new System.Data.SqlClient.SqlParameter("@StarTime",System.Data.SqlDbType.DateTime),
                                       new System.Data.SqlClient.SqlParameter("@FinishTime",System.Data.SqlDbType.DateTime)
                                 };


                   SqlParameter[0].Value = ((DateTimeOffset)starTime).LocalDateTime;
                   SqlParameter[1].Value = ((DateTimeOffset)finishTime).LocalDateTime;

                   DSDecompositionOrders = SqlHelper.ExecuteDataset(conn, CommandType.Text, sqlText.ToString(), SqlParameter);
                   if (conn.State == System.Data.ConnectionState.Open)
                       conn.Close();

                   return DSDecompositionOrders;
               }
           }
           catch (System.Data.SqlClient.SqlException ex)
           {
               throw (new Exception(ex.Message));
           }


       }


       /// <summary>
       /// Gets the decomposition orders.
       /// </summary>
       /// <param name="orders">The orders.</param>
       /// <param name="connectString">The connect string.</param>
       /// <param name="tableName">Name of the table.</param>
       /// <returns></returns>
       protected DataSet GetDecompositionOrders(List<Guid> orders, string connectString, string tableName)
       {
           try
           {
               StringBuilder sqlWhere = new StringBuilder();

               foreach (Guid order in orders)
               {
                   if (sqlWhere.Length > 0)
                       sqlWhere.Append(",");

                   sqlWhere.Append("'" + order.ToString()+ "'");
               }
             

               StringBuilder sqlText = new StringBuilder();
               sqlText.Append(" SELECT OrderID,MatterName,OrderDateTime,FinishDateTime");
               sqlText.Append(" FROM   " + tableName + " [View_Orders] with(NOLOCK)");//readpast

               if (sqlWhere.Length > 0)
                   sqlText.Append(string.Format(" WHERE OrderID IN ( {0} )", sqlWhere));


               using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectString))
               {
                   conn.Open(); 

                   DataSet DSDecompositionOrders = SqlHelper.ExecuteDataset(conn, CommandType.Text, sqlText.ToString());
                   if (conn.State == System.Data.ConnectionState.Open)
                       conn.Close();

                   return DSDecompositionOrders;
               }
           }
           catch (System.Data.SqlClient.SqlException ex)
           {
               throw (new Exception(ex.Message));
           }
       }

        #endregion
     }
}
